The Recordset Object

The Recordset object contains all the data that you read from a database or that you're about to send to it. A Recordset can include several rows and columns of data. Each row is a record, and each column is a field in the record. You can access only one row at a time, the so-called current row or current record. You navigate through a Recordset by changing the current record.

ADO Recordset objects are much more versatile than their DAO and RDO counterparts. For example, you can create an ADO Recordset object yourself, without being connected to a database. Or you can retrieve a Recordset from a database, close the connection, modify the data in the Recordset, and finally reestablish the connection to send all your updates to the server. (These optimistic batch updates were possible in RDO but not in DAO.) You can even save an ADO Recordset to a disk file so that you can restore it later.

The ADO Workbench application provided on the companion CD lets you play with the many properties of the Recordset object. You can also execute its methods and see which events fire. The application decodes the meaning of all the symbolic constants exposed by the ADODB library, as shown in Figure 13-4.

Figure 13-4. You can use the ADO Workbench application to look at a Recordset's properties, execute its methods, and see its events fire; other windows let you browse the Fields collection and the actual contents of the records.

The Recordset object is the richest object in the ADO object model in terms of properties. Again, I'll group the properties according to their functionality rather than by name.

The most significant property of the Recordset object is probably the Source property, which holds the name of the table, the name of the stored procedure, or the text of the SQL query used to populate the Recordset. This property is declared as Variant, which permits you to assign a Command object to it. If you assign a Command object to this property, it returns the contents of the CommandText property of the Command object, not a reference to the Command object itself. The Source property is read/write for closed Recordset objects and read-only after the Recordset has been opened. Here's an example of the Source property:

' Edit this constant to match your directory structure. Const DBPATH = "C:\Program Files\Microsoft Visual Studio\Vb98\NWind.mdb" Dim cn As New ADODB.Connection, rs As New ADODB.Recordset cn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & DBPATH rs.Source = "Employees" rs.Open , cn

You can make your code more concise if you pass the value of this property as the first argument of the Open method:

rs.Open "Employees", cn

When you assign an ADO Command object to the Source property, you can later retrieve a reference to this object through the ActiveCommand property.

To open a Recordset, you must associate it with an existing connection. You can explicitly create this Connection object and assign it to the ActiveConnection property, or you can create it implicitly by assigning a connection string to the ActiveConnection property:

' Edit this constant to match your directory structure. Const DBPATH = "C:Program Files\Microsoft Visual Studio\VB98\NWind.mdb" ' First method: explicit Connection object cn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & DBPATH_ Set rs.ActiveConnection = cn rs.Source = "Employees" rs.Open ' Second method: implicit Connection object rs.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51;" _ & "Data Source= " & DBPATH rs.Source = "Employees" rs.Open

When you create an implicit Connection object, you can later reference it through the ActiveConnection property (for example, to access the Connection's Errors collection). The ActiveConnection property is read-only after the record has been opened or when a Command object has been assigned to the ActiveCommand property.

Yet another way to select the location from which the Recordset should retrieve its data is with the DataSource and DataMember properties. For example, you can link a Recordset object to an ADO Data control by using the following statement:

Set rs.DataSource = Adodc1

You don't need to set any other property, nor do you have to call the Open method (which in fact raises an error). If the data source is a DataEnvironment object, you also need to assign a valid string to the DataMember property; otherwise, an error will occur when setting the DataSource property.

The Recordset's State property returns the current state of the Recordset as a set of bits that can be tested individually:

Value	Description
0-adStateClosed	The Recordset is closed.
1-adStateOpen	The Recordset is open.
2-adStateConnecting	The Recordset is connecting.
4-adStateExecuting	The Recordset is executing a command.
8-adStateFetching	The rows of the Recordset are being fetched.

The last three values apply only when the Recordset object is executing an asynchronous method.

A cursor is a set of records that represent the results of a query. Cursors can contain the actual data or just pointers to records in the database, but the mechanism that retrieves the data is transparent to the programmer. You can specify where the cursor should be created (on the client or on the server workstation), the cursor type, and the locking option.

The CursorLocation property specifies where the cursor is to be created. This property can have one of two values: 2-adUseServer or 3-adUseClient. The value of this property is inherited from the Connection object and can be changed only for closed Recordsets. When you're working with the OLE DB Provider for ODBC Drivers and for SQL Server, the default cursor is a forward-only cursor created on the server. (This type of cursor is also the most efficient.) You need to switch to client-side cursors if you want to create dissociated Recordsets and use an optimistic batch update strategy. Client-side cursors are often a good choice when you have a DataGrid control or another complex control that is bound to the Recordset. In all other cases, server-side cursors are often preferable because they offer better performance and additional cursor types.

The CursorType property tells which type of cursor should be created and is one of the following constants: 0-adOpenForwardOnly, 1-adOpenKeyset, 2-adOpenDynamic, or 3-adOpenStatic. Server-side cursors support all these options, whereas client-side cursors support only 3-adOpenStatic. But if you use a different setting for a client-side cursor, a static cursor is automatically created without raising an error.

A forward-only cursor is the default for server-side cursors and is available only for server-side cursors. As I just mentioned, this type of cursor is the most efficient, especially if you set LockType = adReadOnly and CacheSize = 1. Many programmers and authors refer to this last type of cursor as a "noncursor." In The Hitchhiker's Guide to Visual Basic and SQL Server, William R. Vaughn defines this as a "fire-hose" cursor, to emphasize how fast it is at tossing data to the client application. You don't have to do anything special to use this cursor (or noncursor) because it's the default for ADO. You can navigate a forward-only Recordset only by using the MoveNext method. If you want to get the best performance for an application that needs to update records, you should do all your updates through SQL commands or stored procedures.

Dynamic cursors consist of a set of bookmarks to the actual data in the data source. Any time the client requests a record, ADO uses the bookmark to read the current value, which means that the application always reads the latest value stored by other users. Dynamic cursors are automatically updated when other users add or delete a record or change any record already in the Recordset. Not surprisingly, these cursors are the most expensive cursors in terms of performance and LAN traffic because any time you move to another record a trip to the server is required to retrieve the current values. You can always update data and perform all kinds of navigational methods on dynamic Recordsets, including using bookmarks if the provider supports them. This type of cursor is available only as server-side cursors.

Keyset cursors are similar to dynamic cursors, but they don't include records added by other users. (Changes other users have made to records in the Recordset are visible, though.) You can read and modify all the records in the cursor, but you get an error if you access a record that another user has deleted. The keyset cursor is available only as a server-side cursor.

Static cursors create a fully scrollable snapshot of all the records identified by the Source property, and they are the only type possible for client-side cursors. Because a static cursor is actually a copy of the data coming from the database, changes that other users make aren't visible. Whereas these cursors are less efficient than forward-only cursors and increase the workload on the computer where they reside, their performance is reasonable and they're a good choice, especially when the Recordset doesn't include too many records. A static cursor is usually the best choice for retrieving data from a stored procedure. Depending on the provider and on other settings, this Recordset can even be updatable. You should create client-side static cursors only when the client workstation has enough memory.

The MaxRecords property sets a limit to the number of records that will be returned in the Recordset when you're working with a cursor. The default value is 0, which means that all records will be returned. This property can be written to when the Recordset is closed and is read-only when the Recordset is open.

The CacheSize property sets and returns the number of records that ADO will cache locally when working with cursors. You can adjust the value of this property to fine-tune your application, trading memory for performance. You can assign a new value to this property at any moment, but if the Recordset is already open, the new setting will be used only when ADO needs to fill the local cache—that is, when you move the pointer to the current record to point to a record that isn't in the cache.

All multiuser databases enforce some type of locking strategy. Locks are necessary to prevent multiple users from performing changes on the same record at the same moment, which would probably result in an inconsistent database. Locks are extremely expensive in terms of scalability; when a lock is enforced on a record being modified by a user, no other user can access the same record. Depending on how you write your applications, a lock can significantly degrade performance and can even cause fatal errors if you don't implement a good lock-resolving strategy.

The LockType property indicates which type of lock should be enforced on the data in the database. This enumerated property can be assigned one of the following values: 1-adLockReadOnly, 2-adLockPessimistic, 3-adLockOptimistic, and 4-adLockBatchOptimistic.

The default value for this property is adLockReadOnly, which creates nonupdatable Recordsets. This is the most efficient option because it doesn't impose a write lock on data. It's also the best choice as far as scalability is concerned. Again, a good strategy is to rely on forward-only, read-only noncursors (the default in ADO) when reading data and to do all updates through SQL statements or stored procedures.

When you're using pessimistic locking, ADO tries to lock the record as soon as you enter edit mode, which occurs when you modify one field in the Recordset. It releases the lock only when you issue an Update method or move to another record. While a record is locked, no other user can access it for writing, which severely reduces the potential for the scalability of the application. For this reason, you should never use pessimistic locking when the user interface of your application lets the user freely navigate in the Recordset (unless you want to block all users when any one of them takes a coffee break!). Pessimistic locking is available only for server-side cursors.

Optimistic locking scales up better than pessimistic locking does, but it requires more attention from the programmer. With optimistic locking, ADO locks the current record only while it's being updated, which usually takes a small amount of time.

Optimistic batch locking is a special mode that's available only for client-side static cursors. In optimistic batch locking, you download all the data on the client machine, let the user perform all the necessary changes (including adding and deleting records), and then resubmit all the changes in one single operation. If you decided to go with client-side cursors, optimistic batch locking is the most efficient mode because it reduces network traffic. However, you'll need to implement a strategy for handling conflicts (for example, when two users update the same record), which won't be a trivial task.

For more information about the various types of locking, read the "Locking Issues" section in Chapter 14.

The ultimate purpose in opening a Recordset is to read the values of its rows and columns and possibly to modify them. Recordsets allow you to read and write only the values in the current record, so you need to navigate through the Recordset to access all the records you're interested in.

You can read the values of the fields of the current record through the Fields collection. You can specify which field you're interested in by passing a numeric index or the name of the field:

' Print the names and values of all the fields in the Recordset. Dim i As Integer For i = 0 To rs.Fields.Count _ 1 ' The Fields collection is zero-based. Print rs.Fields(i).Name & " = " & rs.Fields(i).Value Next

You can also use the For Each statement to iterate on all the fields. You can omit the Value property because it's the default property for the Field object.

Dim fld As ADODB.Field For Each fld In rs.Fields Print fld.Name & " = " & fld Next

Unlike DAO and RDO, ADO doesn't support an Edit method, and you can start updating one or more fields of the current record simply by assigning new values to the Field object you want to modify. Moreover, you don't need to explicitly issue an Update method because ADO will automatically execute it for you when you move to another record in the Recordset. These features simplify the structure of the code that reads and updates all the records in a Recordset:

' Convert the contents of the LastName field to uppercase. rs.MoveFirst Do Until rs.EOF rs("LastName") = UCase$(rs("LastName")) rs.MoveNext Loop

You can determine the editing status of a Recordset by querying its EditMode property, which returns one of the following values:

Value	Description
0-adEditNone	No editing operation is in progress.
1-adEditInProgress	One or more fields have been modified, but new values haven't been saved yet.
2-adEditAdd	A new record has been added, but it hasn't been saved to the database yet.
3-adEditDelete	The current record has been deleted.

A number of properties help you understand where you are in the Recordset so that you can enable or disable certain operations or set bookmarks to quickly return to a record you've visited previously. The property in this group that you'll probably use most frequently is EOF, which returns True when the pointer to the current record is positioned after the end of the Recordset. You typically use this property when looping on all the records in the Recordset:

' Count all employees hired before January 1, 1994. rs.MoveFirst Do Until rs.EOF If rs("HireDate") < #1/1/1994# then count = count + 1 rs.MoveNext Loop

BOF is a similar property. It returns True when the record pointer is positioned before the beginning of the Recordset. It's often crucial to know the values of EOF and BOF properties: When either one returns True, most of the Recordset's methods and properties return an error because there's no current record. For example, you can't retrieve a Field's value if the current record is before the beginning or after the end of the Recordset. If both BOF and EOF properties are True, the Recordset is empty.

The Bookmark property lets you retrieve a Variant value that identifies the current record; you can later move back to this record simply by reassigning the same value to the Bookmark property, as this code demonstrates:

Dim mark As Variant mark = rs.Bookmark ' Remember where you are. rs.MoveLast ' Move to the last record. rs("HireDate") = #12/10/1994# ' Assign a new value to the HireDate field. rs.Bookmark = mark ' Return to the marked record.

ADO bookmarks are stored internally as Double values. Even if they are numeric values, you shouldn't assume that you can compare them as if they were numbers. The only arithmetic operation that makes sense with bookmarks is a test for equality, as in the following code:

' Print the names of the employees who were hired on the same ' day as (or later than) the employee whose record is current in the Recordset. Dim mark As Double, curHireDate As Date mark = rs.Bookmark: curHireDate = rs("HireDate") rs.MoveFirst Do Until rs.EOF If rs.Bookmark <> mark Then ' Don't consider the current employee. If rs("HireDate") >= curHireDate Then Print rs("LastName") End If rs.MoveNext Loop ' Move the record pointer back to the record that was current. rs.Bookmark = mark

Moreover, bookmarks can be compared for equality only if they come from the same Recordset object or from a clone Recordset. (See the description of the Clone method later in this chapter.) In all other cases, you shouldn't compare the Bookmark properties of two distinct Recordset objects, even if they are pointing to the same rowset in the same database. For more information about comparing bookmarks, see the description of the CompareBookmarks method in the section "Navigating the Recordset," later in this chapter.

The RecordCount read-only property returns the number of records in the Recordset. Depending on the database engine, the provider, and the type of Recordset, this property can also return -1. This property isn't supported by forward-only Recordsets, for example. If the property is supported, reading its value forces ADO to perform an implicit MoveLast method, so this operation can add a lot of overhead if used with large Recordsets.

The AbsolutePosition property sets or returns a Long value that corresponds to the ordinal position of the current record in the Recordset. (The first record returns 1; the last record returns RecordCount.) It can also return one of the following values: -1-adPosUnknown (position is unknown), -2-adPosBOF (BOF condition), or -3-adPosEOF (EOF condition).

You should never use this property in place of the record number or, worse, instead of the Bookmark property because the AbsolutePosition property varies when records are added to or removed from the Recordset. The most reasonable way to use this property is when you want to provide a scroll bar or a Slider control that lets the user quickly move in the Recordset. In this case, you should set the scroll bar's Min property to 1 and its Max property to rs.RecordCount and then add this code in the scroll bar's Change or Scroll event procedure:

Private Sub HScrollBar1_Change() On Error Resume Next rs.AbsolutePosition = HScrollBar1.Value End Sub

Remember that a scroll bar's Max value can't be higher than 32,767; if you have to deal with more records than that, you should either scale that value or use a Slider control.

Each Recordset is subdivided into pages, and each page can contain a fixed number of records (except the last page, which can be filled only partially). The PageSize property returns the number of records in each page, whereas the PageCount property returns the number of pages in the Recordset. The AbsolutePage property sets or returns the page number of the current record. This property is conceptually similar to the AbsolutePosition property (and supports the same negative values to indicate unknown, BOF, and EOF conditions), but it works with page numbers instead of record numbers. It's most useful when you're implementing advanced strategies to buffer records being read from the database.

You can sort the records in a Recordset by assigning a field list to the Sort property, as in the following example:

' Sort the Recordset on the LastName and FirstName fields. rs.Sort = "LastName, FirstName"

The first field name is the primary sort key, the second field name is the secondary sort key, and so on. By default, records are sorted in ascending order for the selected keys; however, you can opt for descending order by using the DESC qualifier:

' Sort in descending order on the HireDate field. (The employees hired ' most recently should be listed first.) rs.Sort = "HireDate DESC"

This property doesn't affect the order of the records in the data source, but it does affect the order of the records in the Recordset. You can restore the original order by assigning an empty string to this property. I found out that the Sort method works only with client-side static cursors, at least with the OLE DB providers for ODBC, Microsoft Jet, and SQL Server. If you sort on fields that aren't indexed, ADO creates a temporary index for them and deletes the index when you close the Recordset or assign an empty string to the Sort property.

You can filter the records in a Recordset by using the Filter property. You can assign three types of values to this property: an SQL query string, an array of bookmarks, or a constant that indicates which records should appear in the Recordset. The most intuitive way to use this property is to assign it an SQL string. This string is similar to the WHERE clause of a SELECT command, but you have to omit the WHERE. A few examples are shown below.

' Filter out all employees hired before January 1, 1994. rs.Filter = "HireDate >= #1/1/1994#" ' Include only employees born in the 1960s. rs.Filter = "birthdate >= #1/1/1960# AND birthdate < #1/1/1970#" ' Filter in only employees whose last names start with the letter C. rs.Filter = "LastName LIKE 'C*'"

You can use the comparison operators (<, <=, >, >=, =, <>) and the LIKE operator, which supports the * and % wildcards but only at the end of the string argument. You can connect simpler statements using AND and OR logical operators, but you can't perform other operations (such as string concatenations). You can group simpler expressions using parentheses. If a field name contains spaces, you must enclose the name within square brackets. You can use the Filter property in this way with server-side cursors if the provider supports filtering; in all other cases, you should use client-side cursors. Because ADO performs the filtering, you should stick to ADO syntax rules; for example, date values must be enclosed in # symbols, strings must be enclosed in single quotes, and quotes embedded in a string must be doubled. (Here's a brief tip: Use the Replace function to prepare the string quickly.)

If you want to filter a group of records that can't be specified using a simple SQL string, you can pass an array of bookmarks to the Filter property:

' Filter out those employees who were hired when they were over age 35. ReDim marks(1 To 100) As Variant Dim count As Long ' Prepare an array of bookmarks. (Assume that 100 bookmarks are enough.) Do Until rs.EOF If Year(rs("HireDate")) - Year(rs("BirthDate")) > 35 Then count = count + 1 marks(count) = rs.Bookmark End If rs.MoveNext Loop ' Enforce the new filter using the array of bookmarks. ReDim Preserve marks(1 To count) As Variant rs.Filter = marks

Finally, you can assign the Filter property one of these enumerated constants:

Value	Description
0-adFilterNone	Remove the current filter (same as assigning an empty string).
1-adFilterPendingRecords	In batch update mode, view only records that have been modified but not sent to the server yet.
2-adFilterAffectedRecords	View records affected by the most recent Delete, Resync, UpdateBatch, or CancelBatch method.
3-adFilterFetchedRecords	View only the records in the local cache.
5-adFilterConflictingRecords	In batch update mode, view only the records that failed to be committed to the server.

Setting the Filter property to the value 2-adFilterAffectedRecords is the only way to see the records that have been deleted.

The MarshalOption property affects how you send back rows to the server. It can be assigned two enumerated constants: 0-adMarshalAll (ADO sends all the rows to the server, which is the default) or 1-adMarshalModifiedOnly (ADO sends only the records that have been modified). This property is available only on client-side ADOR Recordsets, which are described in the "Remote Data Services" section in Chapter 19.

The Status property is a bit-field value that returns the status of the current record after a batch update operation or another bulk operation has completed. You can test its individual bits using the enumerated properties listed in Table 13-2.

The only Recordset property I haven't yet described is StayInSync, which applies to the child Recordsets of a hierarchical Recordset object. To understand what this property does, you must consider that hierarchical Recordsets expose Field objects that contain child Recordset objects. By default, ADO automatically updates these child Recordsets when the record pointer of the parent Recordset moves to another record. This default behavior is what you want in the vast majority of cases, but at times you'd like to save the contents of a child Recordset for later; and in a sense, you want to detach it from its parent Recordset. Well, you can separate the parent and child Recordsets by setting the child Recordset's StayInSync property to False. Another way to reach the same result is to use the Clone method to create a copy of the child Recordset: If you use this method, the cloned Recordset won't be updated when the parent Recordset moves to another record. For more information, see the section "Heirarchical Recordsets" in Chapter 14.

The Recordset object exposes several methods. Again, I'll describe them in groups, according to their purpose.

Table 13-2. The constants to use when testing the Status property.

Constant	Value	Description
adRecOK	0	The record was successfully updated.
adRecNew	1	The record is new.
adRecModified	2	The record was modified.
adRecDeleted	4	The record was deleted.
adRecUnmodified	8	The record wasn't modified.
adRecInvalid	&H10	The record wasn't saved because its bookmark is invalid.
adRecMultipleChanges	&H40	The record wasn't saved because it would affect multiple records.
adRecPendingChanges	&H80	The record wasn't changed because it refers to a pending insert.
adRecCanceled	&H100	The record wasn't saved because the operation was canceled.
adRecCantRelease	&H400	The record wasn't saved because of existing record locks.
adRecConcurrencyViolation	&H800	The record wasn't saved because optimistic concurrency was in use.
adRecIntegrityViolation	&H1000	The record wasn't saved because it would violate integrity constraints.
adRecMaxChangesExceeded	&H2000	The record wasn't saved because there were too many pending changes.
adRecObjectOpen	&H4000	The record wasn't saved because of a conflict with an open storage object.
adRecOutOfMemory	&H8000	The record wasn't saved because of an out-of-memory error.
adRecPermissionDenied	&H10000	The record wasn't saved because the user had insufficient permissions.
adRecSchemaViolation	&H20000	The record wasn't saved because it doesn't match the structure of the database.
adRecDBDeleted	&H40000	The record had already been deleted from the database.

If you want to read the data in a Recordset, you must open it first, which you do with the Open method:

Open [Source], [ActiveConnection], [CursorType], [LockType], [Options]

The arguments of the Open method have the same meaning as the properties with the same names: Source is the name of a table or a stored procedure, an SQL query, or a reference to an ADO Command object; ActiveConnection is a reference to an ADO Connection object or to a connection string that identifies the provider and the data source; CursorType specifies which type of cursor you want to create (forward-only, static, keyset, or dynamic); and LockType is the type of locking you want to enforce (read-only, pessimistic, optimistic, or optimistic batch). Options is the only argument that doesn't correspond to a Recordset property: It explains to ADO what you're passing in the Source argument and can be one of the following enumerated constants:

Value	Description
1-adCmdText	Textual SQL query
2-adCmdTable	Database table
4-adCmdStoredProc	Stored procedure
8-adCmdUnknown	Unspecified; the provider will determine the correct type
256-adCmdFile	A persistent Recordset
512-adCmdTableDirect	A database table opened directly

Even if in most cases the provider can understand what the source of the Recordset is without your help, you can often speed up the Open method by assigning a correct value to this argument.

All these arguments are optional. However, ADO can't open the Recordset if you don't provide enough information. For example, you can omit the Source argument if you've assigned a value to the Source property, and you can omit the ActiveConnection argument if you've assigned a value to the ActiveConnection property or if you're using an ADO Command object as the source for this Recordset (in which case the ActiveConnection argument is inherited from that Command object). If you omit the third or fourth argument, by default, the Open method creates a forward-only, read-only Recordset, which is the most efficient Recordset type that ADO supports. You can't specify the cursor position in the Open method, and if you want to create a client-side cursor, you must assign the adUseClient constant to the CursorLocation property before opening the Recordset. Below are a few examples that show the Open method in action.

' Edit this constant to match your directory structure. Const DBPATH = "C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb" ' All the following examples use these variables. Dim cn As New ADODB.Connection, rs As New ADODB.Recordset Dim connString As String, sql As String connString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & DBPATH ' Open the Recordset using an existing Connection object. cn.Open connString rs.Open "Employees", cn, adOpenStatic, adLockReadOnly, adCmdTable ' Open the Recordset using a Connection object created on the fly. ' This creates a forward-only, read-only Recordset. rs.Open "Employees", connString, , , adCmdTable ' After the Recordset has been opened, you can query the properties ' of the implicit Connection object. Print "Current Connection String = " & rs.ActiveConnection.ConnectionString ' Select only the employees who were born in the 1960s or later. sql = "SELECT * FROM Employees WHERE BirthDate >= #1/1/1960#" rs.Open sql, connString, , , adCmdText

You can also open a record that you previously saved to a disk file using the Save method: In this case, the first argument of the Open method is the complete name and path of the file, and you should pass the adCmdFile constant to the Options argument.

The Options argument supports two more constants for asynchronous operations. The value 16-adAsyncExecute executes the query asynchronously: The control goes back to the application immediately, and ADO continues to populate the Recordset until the local cache is filled with data. The value 32-adAsyncFetch tells ADO that after filling the local cache with data, it should fetch the remaining records asynchronously. When all the records have been retrieved, ADO fires a FetchComplete event.

You can cancel an asynchronous operation at any moment by issuing a Cancel method. If no asynchronous operations are pending, this method does nothing and no error is raised.

When you're done with a Recordset, you should close it using its Close method. This method doesn't take any argument. ADO automatically closes a Recordset when no more variables are pointing to it. When a Recordset is closed, ADO releases all the locks and the memory allocated to its cursor (if it has one). You can't close a Recordset if an edit operation is in progress (that is, if you modified the value of one or more fields and haven't committed the changes). You can reopen a closed Recordset by using the same or different values for its Source, CursorType, MaxRecords, CursorPosition, and LockType properties. (These properties are read-only while the Recordset is open.)

You can create a Recordset also by using the Clone method to create a copy of an existing Recordset:

Dim rs2 As ADODB.Recordset Set rs2 = rs.Clone(LockType)

The optional LockType argument tells which type of lock you want to enforce on the new Recordset. The cloned record can be opened only with the same lock type as the original record has (in this case, you just omit the argument) or in read-only mode (you pass the adLockReadOnly constant). Cloning a Recordset is more efficient than creating another Recordset against the same data source. Any modified value in one Recordset is immediately visible to all its clones regardless of their cursor type, but all the Recordsets in the group can be scrolled and closed independently from one another. If you issue a Requery method against the original Recordset, its clones aren't synchronized any longer. (The opposite isn't true, however: If you Requery the clones, they are still synchronized with the original Recordset.) Keep in mind that only Recordsets that support bookmarks can be cloned and that you can compare bookmarks defined in a Recordset and its clones.

ADO offers two methods for repopulating a Recordset without closing and reopening it. The Requery method reexecutes the Recordset query. This method is especially useful with parameterized queries against an SQL Server database when you aren't using a Command object because it tells ADO to reuse the temporary stored procedure that SQL Server created when the Recordset was opened the first time. The Requery method accepts the adAsyncExecute option to run the query asynchronously. When the query completes, a RecordsetChangeComplete event fires. The Requery method lets you reexecute the query, but you can't modify any property that affects the type of the cursor (CursorType, CursorLocation, LockType, and so on) because these properties are read-only when the Recordset is open. To change these properties, you must close and then reopen the Recordset.

The Resync method refreshes the Recordset from the underlying database without actually reexecuting the query. Its syntax is as follows:

Resync [AffectRecords], [ResyncValues]

AffectRecords tells which records should be refreshed and can be one of the following constants:

Value	Description
1-adAffectCurrent	Refreshes the current record only
2-adAffectGroup	Refreshes the records that satisfy the current Filter property, which should have been assigned one of the supported enumerated constants
3-adAffectAll	Refreshes the entire Recordset (the default)

ResyncValues can be one of the following values:

Value	Description
1-adResyncUnderlyingValues	Reads the most recent values from the database and puts them in the UnderlyingValue properties of the Field objects
2-adResyncAllValues	Reads the most recent values and puts them in the Value properties of the Field objects (the default)

The effect of these two options is completely different: adResyncUnderlyingValues preserves the old data and doesn't cancel pending changes; adResyncAllValues cancels pending changes (as if a CancelBatch method had been issued).

Because the Resync method doesn't reexecute the query, you'll never see new records added by other users in the meantime. This method is especially useful with forward-only or static cursors when you want to be sure you're working with the most recent values. Any conflict during the resynchronization process—for example, another user has deleted a record—fills the Errors collection with one or more warnings. When using client-side cursors, this method is available only for updatable Recordsets.

To read the values of the current record, you simply query the Fields collection as shown here:

' Print employee's first and last name. Print rs.Fields("FirstName").Value, rs.Fields("LastName").Value

Because Fields is the default property for the Recordset object, you can omit it and access the field simply by using its name or its index. Similarly, you can drop the Value property because it's the default member of the Field object:

Print rs("FirstName"), rs("LastName")

You display the values of all the fields in the current record by iterating on the Fields collection. You can use the Field's index in a For…Next loop or a Field object variable in a For Each…Next loop:

' The first method uses a regular For…Next loop. For i = 0 To rs.Fields.Count _ 1 Print rs.Fields(i).Name & " = " & rs(i) Next ' The second method uses a For Each…Next loop. Dim fld As ADODB.Field For Each fld In rs.Fields Print fld.Name & " = " & fld.Value Next

ADO also offers more efficient ways to retrieve data. The GetRows method returns a two-dimensional array of Variants, where each column corresponds to a record in the Recordset and each row corresponds to a field in the record. This method has the following syntax:

varArray = rs.GetRows([Rows], [Start], [Fields])

Rows is the number of records you want to read; use -1 or omit this argument if you want to retrieve all the records in the Recordset. Start is a bookmark that indicates the first record to be read; it can also be one of the following enumerated constants: 0-adBookmarkCurrent (the current record), 1-adBookmarkFirst (the first record), or 2-adBookmarkLast (the last record).

When processing the data in the target Variant array, you should remember that data is stored somewhat counterintuitively: The first subscript in the array identifies the Recordset's field (which is usually thought of as a column), and the second subscript identifies the Recordset's record (which is usually thought of as a row). Here's an example that loads three fields from all the records in the Recordset:

Dim values As Variant, fldIndex As Integer, recIndex As Integer values = rs.GetRows(, , Array("LastName", "FirstName", "BirthDate")) For recIndex = 0 To UBound(values, 2) For fldIndex = 0 To UBound(values) Print values(fldIndex, recIndex), Next Print Next

The GetRows method is usually noticeably faster than an explicit loop that reads one record at a time, but if you use this method you must ensure that the Recordset doesn't contain too many records; otherwise, you can easily fill up all your physical memory with a very large Variant array. For the same reason, be careful not to include any BLOB (Binary Large Object) or CLOB (Character Large Object) fields in the field list; if you do, your application will almost surely bomb, especially with larger Recordsets. Finally, keep in mind that the Variant array returned by this method is zero-based; the number of returned records is UBound(values,2)+1, and the number of returned fields is UBound(value, 1)+1.

The GetString method is similar to GetRows, but it returns multiple records as a single string. GetString has the following syntax:

GetString([Format], [NumRows], [ColDelimiter], [RowDelimiter], [NullExpr])

Format is the format for the result. GetString potentially supports more formats, but the only format currently supported is 2-adClipString, so you don't really have any choice. NumRows is the number of rows to retrieve. (Use -1 or omit this argument to read all the remaining records.) ColDelimiter is the delimiter character for the columns. (The default is the Tab character.) RowDelimiter is the delimiter character for the records. (The default is the carriage return.) NullExpr is the string to be used for Null fields. (The default is the empty string.) The documentation states that the last three arguments can be used only if Format = adClipString, but this warning doesn't make much sense because, as I just mentioned, this format is the only one currently supported. Here's an example that uses the GetString method to export data in a semicolon-delimited text file:

Dim i As Long Open "datafile.txt" For Output As #1 For i = 0 To rs.Fields.Count _ 1 ' Export field names. If i > 0 Then Print #1, ";"; Print #1, rs.Fields(i).Name; Next Print #1, "" rs.MoveFirst ' Export data. Print #1, rs.GetString(, , ";", vbCrLf); ' Don't add an extra CR-LF here. Close #1

The GetString method doesn't permit you to export only a subset of the fields, nor does it permit you to modify the order of the exported fields. If you need these additional capabilities, you should use the GetRows method and build the result string yourself.

When you open a Recordset, the current record pointer points to the first record unless the Recordset is empty (in which case, both the BOF and EOF properties return True). To read and modify values in another record, you must make that record the current record, which you usually do by executing one of the Movexxxx methods exposed by the Recordset object. MoveFirst moves to the first record in the Recordset, MoveLast moves to the last record, MovePrevious moves to the previous record, and MoveNext moves to the next record. You typically provide users with four buttons that let them navigate the Recordset. Executing a MovePrevious method when BOF is True or executing a MoveNext method when EOF is True raises an error; therefore, you have to trap these conditions before moving to the previous or next record:

Private Sub cmdFirst_Click() rs.MoveFirst End Sub Private Sub cmdPrevious_Click() If Not rs.BOF Then rs.MovePrevious End Sub Private Sub cmdNext_Click() If Not rs.EOF Then rs.MoveNext End Sub Private Sub cmdLast_Click() rs.MoveLast End Sub

The MoveFirst and MoveNext methods are commonly used in loops that iterate on all the records in the Recordset, as shown in the following example:

rs.MoveFirst Do Until rs.EOF total = total + rs("UnitsInStock") * rs("UnitPrice") rs.MoveNext Loop Print "Total of UnitsInStock * UnitPrice = " & total

ADO also supports a generic Move method, whose syntax is:

Move NumRecords, [Start]

NumRecords is a Long value that specifies the number of records to skip toward the end (if positive) or the beginning (if negative) of the Recordset. The move is relative to the record identified by the Start argument, which can be a bookmark value or one of the following enumerated constants:

Value	Description
0-adBookmarkCurrent	The current record
1-adBookmarkFirst	The first record in the Recordset
2-adBookmarkLast	The last record in the Recordset

As you can see below, the Move method embodies the functionality of the four Movexxxx methods I've described previously.

rs.Move 0, adBookmarkFirst ' Same as MoveFirst rs.Move _1 ' Same as MovePrevious rs.Move 1 ' Same as MoveNext rs.Move 0, adBookmarkLast ' Same as MoveLast rs.Move 10, adBookmarkFirst ' Move to the tenth record. rs.Move -1, adBookmarkLast ' Move to the next to the last record. rs.Move 0 ' Refresh the current record.

If you specify a negative offset that points to a record before the first record, the BOF property becomes True and no error is raised. Similarly, if you specify a positive offset that points to a record after the last record, the EOF property is set to True and no error is raised. Interestingly, you can specify a negative offset even with forward-only Recordsets: If the target record is still in the local cache, no error occurs. (You can't use MovePrevious with forward-only Recordsets, regardless of whether the previous record is in the cache.)

You can also navigate a Recordset using the Bookmark and AbsolutePosition properties. ADO also provides a CompareBookmarks method that lets you compare bookmarks coming from the same Recordset or from a cloned Recordset. This method has the following syntax:

result = CompareBookmarks(Bookmark1, Bookmark2)

result can receive one of these values:

Value	Description
0-adCompareLessThan	The first bookmark refers to a record that precedes the record the second bookmark refers to.
1-adCompareEqual	The two bookmarks point to the same record.
2-adCompareGreaterThan	The first bookmark refers to a record that follows the record the second bookmark refers to.
3-adCompareNotEqual	The two bookmarks refer to different records, but the provider can't determine which one comes first.
4-adCompareNotComparable	The bookmarks can't be compared.

ADO differs from DAO and RDO in that the Update method isn't really necessary: All you have to do to modify a record is assign a new value to one or more Field objects and then move to another record. The ADO Update method supports the capability to update multiple fields at once, using the following syntax:

Update [fields] [, values]

fields is a Variant containing a single field name, a field index, or an array of field names or indexes. values is a Variant containing a single value or an array of values. These arguments are optional, but you can't omit just one of the two: If provided, they must contain the same number of arguments. The following example demonstrates how you can update multiple fields using this syntax:

' Update four fields in one operation. rs.Update Array("FirstName", "LastName", "BirthDate", "HireDate"), _ Array("John", "Smith", #1/1/1961#, #12/3/1994#)

Because an update operation is automatically performed if one or more fields in the current record have been modified, ADO provides the CancelUpdate method to cancel such changes and leave the current record unmodified. You can use the Update and CancelUpdate methods together to offer the user a chance to confirm or cancel changes to the current record:

If rs.EditMode = adEditInProgress Then If MsgBox("Do you want to commit changes?", vbYesNo) = vbYes Then rs.Update Else rs.CancelUpdate End If End If

You can add new records to the Recordset with the AddNew method. This method is similar to the Update method in that it supports two syntax forms, with or without arguments. If you don't pass an argument, you create a new record at the end of the Recordset and you're supposed to assign values to its fields using the Fields collection:

rs.AddNew rs("FirstName") = "Robert" rs("LastName") = "Doe rs("BirthDate") = #2/5/1955# rs.Update

You don't need an explicit Update method after an AddNew method—any Movexxxx method will do. In the second syntax form, you pass the AddNew method a list of fields and a list of values; in this case, no update is necessary because values are immediately committed to the database:

' This statement has the same effect as the previous code snippet. rs.AddNew Array("FirstName", "LastName", "BirthDate"), _ Array("Robert", "Doe", #2/5/1955#)

After you commit changes with an Update method, the record you've just added becomes the current record. If you issue a second AddNew method, you commit changes automatically to the record added just before, as if you had executed a Movexxxx method. Depending on the type of cursor, it's possible that the record you've added doesn't appear in the Recordset immediately and that you'll have to execute a Requery method to see it.

You can delete the current record by executing the Delete method. This method accepts an optional argument:

rs.Delete [AffectRecords]

If AffectRecords is 1-adAffectCurrent or is omitted, only the current record is deleted. When you delete a record, it's still the current record but it can't be accessed any longer, so you'd better move on to another record:

rs.Delete rs.MoveNext If rs.EOF Then rs.MoveLast

You can delete a group of records by assigning an enumerated constant to the Filter property and then issuing a Delete method with the AffectRecords argument set to 2-adAffectGroup:

' After a batch update attempt, delete all the records that failed ' to be transferred to the server. rs.Filter = adFilterConflictingRecords rs.Delete adAffectGroup rs.Filter = adFilterNone ' Remove the filter.

You should nest your delete operations in a transaction if you want to give your users the chance to undelete them.

The Find method provides a simple way to move to a record in the Recordset that matches search criteria. This method has the following syntax:

Find Criteria, [SkipRecords], [SearchDirection], [Start]

Criteria is a string that contains the search condition, which consists of a field name followed by an operator and a value. The supported operators are = (equal), < (less than), > (greater than), and LIKE (pattern matching). The value can be a string enclosed by single quotes, a number, or a date value enclosed by # characters. SkipRecord is an optional number that indicates how many records should be skipped before starting the search: Positive values skip forward (toward the end of the Recordset), and negative values skip backward (toward the beginning of the Recordset). SearchDirection indicates the direction in which the search must proceed; you can use the values 1-adSearchForward (the default) or -1-adSearchBackward. Start is an optional bookmark that specifies the record from which the search should begin. (The default is the current record.)

In most cases, you can omit all the arguments except the first one, which results in a search that starts from the current record (included) and goes toward the end of the database. If the search is successful, the record that matches the search criteria becomes the current record; if the search fails, the current record is past the last record in the Recordset (or before the first record, if Search = adSearchBackward). Passing a nonzero value to the SkipRecord argument is necessary when you want to restart the search after you've found a match, as the following code snippet demonstrates:

' Search all the employees who were hired after January 1, 1994. rs.MoveFirst rs.Find "HireDate > #1/1/1994#" Do Until rs.EOF Print rs("LastName"), rs("BirthDate"), rs("HireDate") ' Search the next record that meets the criteria, but skip the current one. rs.Find "HireDate > #1/1/1994#", 1 Loop

The LIKE operator accepts two wildcard symbols: * (asterisk) matches zero or more characters, and _ (underscore) matches exactly one character. Comparisons aren't case sensitive and aren't affected by the Option Compare directive. Here are a few examples:

rs.Find "FirstName LIKE 'J*'" ' Matches "Joe" and "John". rs.Find "FirstName LIKE 'J__'" ' Matches "Joe" but not "John". rs.Find "FirstName LIKE '*A*'" ' Matches "Anne", "Deborah", and "Maria". rs.Find "FirstName LIKE '*A'" ' This gives an error: a bug?

If you open a Recordset with the adLockBatchOptimistic option, all the rules stated so far about record updating are void. When working with optimistic batch updates, you're actually working with a cursor on the client workstation. You can read it even if the connection with the server doesn't exist anymore, and you can modify it without committing the changes to the server (not immediately at least). In optimistic batch mode, the implicit or explicit Update method affects only the local cursor, not the real database. This helps keep the network traffic to a minimum and greatly improves overall performance.

When you're ready to commit the changes to the database on the server, you issue an UpdateBatch method, which has the following syntax:

UpdateBatch [AffectRecords]

You should assign the AffectRecords argument one of the constants listed below.

Value	Description
1-adAffectCurrent	Updates only the current record
2-adAffectGroup	Updates all the modified records that satisfy the current Filter property, which must have been assigned one of the supported enumerated constants
3-adAffectAll	Updates all modified records in the Recordset (the default)
4-adAffectAllChapters	Updates all the chapters in a hierarchical Recordset

The adAffectAll setting is hidden in the ADODB type library. If you issue the UpdateBatch method while in edit mode, ADO commits the changes to the current record and then proceeds with the batch update.

The Visual Basic documentation states that if there is a conflict and one or more records can't be successfully updated, ADO fills the Errors collection with warnings but doesn't raise an error in the application. ADO raises an error only if all the records fail to update. Some tests prove, however, that when there is one conflicting record, the error &H80040E38, "Errors occurred," is returned to the application. You can then set the Filter property to the adFilterConflictingRecords value to see which records weren't updated successfully.

You can cancel a batch update using the CancelBatch method, which has the following syntax:

CancelBatch [AffectRecords]

AffectRecords has the same meaning here as it has with the UpdateBatch method. If the Recordset hasn't been opened with the adLockBatchOptimistic option, any value other than 1-adAffectCurrent raises an error. If you're in edit mode, the CancelBatch method cancels the updates to the current record first and then cancels the changes to the records affected by the AffectRecords argument. After a CancelBatch method is completed, the current record position might be undetermined, so you should use the Movexxxx method or the Bookmark property to move to a valid record.

When performing batch update operations on the client machine, you don't need to keep the connection to the database active. In fact, you can set the Recordset's ActiveConnection property to Nothing, close the companion Connection object, let your user browse and update the data, and then reestablish the connection when he or she is ready to post the updates to the database. For more information about batch updates, see the section "Optimistic Client-Batch Updates" in Chapter 14.

One of the most intriguing features of the ADO Recordset object is that you can save it to a regular disk file and then reopen it when necessary. This feature is advantageous in many situations—for example, when you're performing batch updates or when you want to postpone the processing of a Recordset. You don't even need to reopen the Recordset with the same application that saved it. For example, you can save a Recordset to a file and later process it with a report application that sends the output to a printer during off hours. The key to this capability is the Save method, which has the following syntax:

Save [FileName], [PersistFormat]

The first argument is the name of the file in which the Recordset should be saved, and the second argument is the format in which the Recordset should be saved. ADO 2.0 supports only the Advanced Data TableGram (ADTG), so you should specify the constant 0-adPersistADTG or omit the argument. Although the syntax of the Save method is rather intuitive, it includes some subtle details that you must account for when you're working with persistent Recordsets:

• In general, persistent Recordsets should use client-side cursors, so you should change the value of the CursorLocation property, whose default value is adUseServer. Some providers might support this capability with server-side cursors, however.
• You need to specify the filename only the first time you save the Recordset to a file; in all subsequent saves, you must omit the first argument if you want to save to the same data file. If you don't omit the argument, a run-time error occurs.
• An error occurs if the file already exists, so you should test for its existence before issuing the Save method and manually delete it if necessary.
• Save doesn't close the Recordset, so you can continue to work with it and save the most recent changes by issuing additional Save methods without the FileName argument. The file is closed only when the Recordset is also closed; in the meantime, other applications can read the file but can't write to it.
• After you've saved to a file, you can specify a different filename to save to a different file; however, this operation doesn't close the original file. Both files remain open until the Recordset is closed.
• If the Filter property is in effect, only the records that are currently visible are saved. This feature is useful for postponing the processing of records that failed to be committed to the database during a batch update operation.
• If the Sort property isn't an empty string, the records will be saved in the sorted order.
• If the Save method is issued while an asynchronous operation is in progress, the method doesn't return until the asynchronous operation is completed.
• After a Save operation, the current record will be the first record in the Recordset.

When opening a persistent Recordset, you should use the adCmdFile value in the Option argument of the Open method:

' Save a Recordset to a file, and then close both the file ' and the Recordset. rs.Save "C:\datafile.rec", adPersistADTG rs.Close '... ' Reopen the persistent Recordset. rs.Open "C:\datafile.rec", , , , adCmdFile

ADO Recordsets support multiple queries in the Source property or in the Source argument of the Open method if the provider also supports multiple queries. You specify multiple SELECT queries, or even SQL action queries, by using the semicolon as a separator, as shown here:

rs.Open "SELECT * FROM Employees;SELECT * FROM Customers"

When the Open method completes its execution, the Recordset object contains all the records from the first query, and you can process these records as you would with a regular Recordset. When you're done with the records, you can retrieve them from the second query using the NextRecordset method:

Dim RecordsAffected As Long Set rs = rs.NextRecordset(RecordsAffected)

The argument is optional; if specified, it should be a Long variable. This variable receives the number of records that have been affected by the current operation (which might also be an SQL command that doesn't return a Recordset). Although the syntax permits you to assign the result of the NextResult method to another Recordset variable, as of this writing, no provider supports this functionality and the original contents of the Recordset are always discarded. If this functionality becomes available, it will be possible to assign each Recordset object to a distinct object variable and process all the Recordsets simultaneously.

Here are a few details to be aware of when you're working with multiple Recordsets:

• Each query is executed only when the NextRecordset method requests it; therefore, if you close the Recordset before processing all the pending commands, the corresponding queries or action commands are never executed.
• If a row-returning query doesn't return any record, the resulting Recordset is empty. You can test this condition by checking whether both the BOF and EOF properties return True.
• If the pending SQL command doesn't return any row, the resulting Recordset will be closed. You can test this condition with the State property.
• When no more commands are pending, the NextRecordset method returns Nothing.
• You can't call the NextResult method if an edit operation is in progress; to avoid errors, you should issue an Update or CancelUpdate method first.
• If one or more of the SQL commands or queries require parameters, you should fill the Parameters collection with all the required parameter values, and they should be in the order expected by the commands or queries.
• The provider must support multiple queries. For example, the provider for Microsoft Jet databases doesn't support them. The providers for SQL Server seem to support this functionality only with static client-side cursors or with server-side "cursorless" Recordsets.

Here's an example of a code framework that you can use when working with multiple Recordsets:

Dim RecordsAffected As Long rs.Open Do If rs Is Nothing Then ' No more Recordsets, so exit. Exit Do ElseIf (rs.State And adStateOpen) = 0 Then ' It was a non-row-returning SQL command. ... Else ' Process the Recordset here. ... End If Set rs.NextRecordset(RecordsAffected) Loop

Not all types of Recordsets support all the features that I've described so far. Instead of having you guess which features are supported and which aren't, the ADO Recordset object exposes the Supports method, which accepts a bit-field argument and returns True only if the Recordset supports all the features indicated in the argument. For example, you can test whether the Recordset supports bookmarks using the following code:

If rs.Supports(adBookmark) Then currBookmark = rs.Bookmark

The argument to the Supports method can include one or more of the constants listed in Table 13-3. You don't need to issue multiple Supports methods if you want to test multiple features:

If rs.Supports(adAddNew Or adDelete Or adFind) Then ' The Recordset supports the AddNew, Delete, and Find methods. End If

Don't forget that if this method returns True you're only sure that ADO supports the requested operation, not that the OLE DB provider necessarily supports it under all circumstances.

Constant	Value	Description
adHoldRecords	&H100	Support for reading more records or changing the next retrieve position without committing pending changes
adMovePrevious	&H200	Support for the MoveFirst and MovePrevious methods and for Move and GetRows with backward moves
adBookmark	&H2000	Support for the Bookmark property
adApproxPosition	&H4000	Support for the AbsolutePosition and AbsolutePage properties
adUpdateBatch	&H10000	Support for the UpdateBatch and CancelBatch methods
adResync	&H20000	Support for the Resync method
adNotify	&H40000	Support for notifications
adFind	&H80000	Support for the Find method
adAddNew	&H1000400	Support for the AddNew method
adDelete	&H1000800	Support for the Delete method
adUpdate	&H1008000	Support for the Update method

The ADO Recordset object exposes 11 events. These events allow you to take complete control of what's happening behind the scenes. By writing code for these events, you can leverage asynchronous queries, trap the instant when a field or a record is modified, and even add data when the user reaches the end of the Recordset. The ADO Workbench application is especially helpful when watching events because it automatically converts all the enumerated constants to their symbolic names.

The FetchProgress event is fired periodically during a lengthy asynchronous operation. You can use it to show the user a progress bar that indicates the percentage of records retrieved:

Private Sub rs_FetchProgress(ByVal Progress As Long, _ ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset)

The Progress parameter is the number of records retrieved so far. MaxProgress is the total number of expected records. adStatus is the usual status parameter. pRecordset is a reference to the Recordset object that is raising the event. (In Visual Basic, you never need to use this argument because you already have a reference to the Recordset.)

When the retrieval of records is completed, ADO fires a FetchComplete event. If the adStatus parameter is equal to adStatusErrorsOccurred, you can query the error through the pError parameter:

Private Sub rs_FetchComplete(ByVal pError As ADODB.error, _ adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset) End Sub

Each time the current record changes, a WillMove event fires, soon followed by a MoveComplete event, as you can see in the code below.

Private Sub rs_WillMove(ByVal adReason As ADODB.EventReasonEnum, _ adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset)

The adReason parameter tells why this event has been fired. It can be one of the constants listed in Table 13-4. Unless adStatus is set to adStatusCantDeny, you can cancel the operation by assigning the value adStatusCancel to adStatus.

When the move operation has completed (or when it has been canceled), a MoveComplete event fires:

Private Sub rs_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, _ ByVal pError As ADODB.error, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset)

The adReason and adStatus parameters have the same meanings as they have in the WillMove event: If adStatus is equal to adStatusErrorOccurred, the pError object contains information about the error; otherwise, pError is Nothing. You can cancel further notifications by setting adStatus to adStatusUnwantedEvent.

Table 13-4. The values of the adReason parameter in Recordset's events.

Value	Constant
1	adRsnAddNew
2	adRsnDelete
3	adRsnUpdate
4	adRsnUndoUpdate
5	adRsnUndoAddNew
6	adRsnUndoDelete
7	adRsnRequery
8	adRsnResynch
9	adRsnClose
10	adRsnMove
11	adRsnFirstChange
12	adRsnMoveFirst
13	adRsnMoveNext
14	adRsnMovePrevious
15	adRsnMoveLast

When the program attempts to move past the end of the Recordset, possibly as a result of a MoveNext method, an EndOfRecordset error fires:

Private Sub rs_EndOfRecordset(fMoreData As Boolean, _ adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset)

ADO lets you add new records to the Recordset when this event fires. If you want to take advantage of this opportunity, just execute an AddNew method, fill the Fields collection with data, and then set the fMoreData parameter to True to let ADO know that you've added new records. As usual, you can cancel the operation that caused the move by setting the adStatus parameter to adStatusCancel, unless the adStatus parameter contains the value adStatusCantDeny.

Any time ADO is about to modify one or more fields in a Recordset, it fires the WillChangeField event:

Private Sub rs_WillChangeField(ByVal cFields As Long, _ ByVal Fields As Variant, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset)

cFields is the number of fields that are about to be modified, and Fields is an array of Variants that contains one or more Field objects with pending changes. You can set adStatus to adStatusCancel to cancel the pending update operation, unless it contains the adStatusCantDeny value.

When the update operation is completed, ADO fires a FieldChangeComplete event, which receives the same parameters plus the pError object that lets you investigate any error raised in the meantime (if adStatus is equal to adStatusErrorOccurred):

Private Sub rs_FieldChangeComplete(ByVal cFields As Long, _ ByVal Fields As Variant, ByVal pError As ADODB.error, _ adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset)

When one or more records are about to change, ADO fires a WillChangeRecord event:

Private Sub rs_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, _ ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset)

adReason is one of the enumerated constants listed in Table 13-4, cRecords is the number of records that are going to be modified, and adStatus is the parameter that you can set to adStatusCancel to cancel the operation (unless the adStatus parameter contains the adStatusCantDeny value).

When the update operation is completed, ADO fires a RecordChangeComplete event:

Private Sub rs_RecordChangeComplete( _ ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, _ ByVal pError As ADODB.error, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset)

All the parameters have the same meanings as they do in the WillChangeRecord event. If adStatus is adStatusErrorOccurred, you can query the pError object to find out what went wrong, and you can reject further notifications by setting adStatus to adStatusUnwantedEvent. These two events can occur because of an Update, UpdateBatch, Delete, CancelUpdate, CancelBatch, or AddNew method. During this event, the Filter property is set to the value adFilterAffectedRecords and you can't change it.

Whenever ADO is about to perform an operation that's going to change the contents of the Recordset as a whole—such as Open, Requery, and Resync methods—a WillChangeRecordset event fires:

Private Sub rs_WillChangeRecordset( _ ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset)

adReason is one of the constants listed in Table 13-4, and adStatus has the usual meaning. If this parameter isn't equal to adStatusCantDeny, you can cancel the operation by setting it to the value adStatusCancel.

When the update operation is complete, ADO fires a RecordsetChangeComplete event:

Private Sub rs_RecordsetChangeComplete( _ ByVal adReason As ADODB.EventReasonEnum, _ ByVal pError As ADODB.error, _ adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

The parameters here mean the same as they do in the WillChangeRecordset event. One undocumented behavior you should keep in mind is that with a forward-only Recordset, the WillChangeRecordset and RecordsetChangeComplete events also fire whenever you execute the MoveNext method because you're working with a cursorless Recordset; each time you move to another record, ADO re-creates the Recordset object. In general, with any Recordset that doesn't support bookmarks, these events fire whenever the local cache has to be refilled (with a frequency that therefore depends on the CacheSize property).